set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
-- **************************************************************************
--    Project Name:   转邮监控汇总
--    Job Name:       jms_dm.dm_tab_transfer_mail_network_summary_dt
--    Description :   转邮监控汇总
--    Author :        申家明
--    date：          2022/08/05
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_tab_transfer_mail_network_summary_dt << [
--       jms_dm.dm_tab_transfer_mail_waybill_detail_dt
--    ]
-- **************************************************************************
insert overwrite table jms_dm.dm_tab_transfer_mail_network_summary_dt partition (dt)
select date_time                                   as date_time        --日期
     , scan_site_code                              as scan_site_code   --扫描站点
     , input_site                                  as input_site       --扫描站点
     , agent_code                                  as agent_code       --代理区code
     , agent_name                                  as agent_name       --代理区名称
     , count(1)                                    as transfer_num     --转邮扫描量
     , sum(del_falg)                               as yes_del_num      --转邮打标
     , count(1) - sum(del_falg)                    as no_del_num       --转邮未打标
     , sum(arr_falg)                               as cnyz_arr_num     --邮政到件量
     , sum(sig_falg)                               as all_sig_num      --总签收量
     , sum(cnyz_sig_falg)                          as cnyz_sig_num     --邮政签收量
     , sum(sig_falg) - sum(cnyz_sig_falg)          as network_sign_num --网点签收量
     , sum(if(arr_falg = 1 or cnyz_sig_falg = 1, 1, 0)) as remark1          --轨迹完整量：（邮政揽收量+邮政签收量）按单号去重
     , ''                                          as remark2
     , date_time                                   as dt
from jms_dm.dm_tab_transfer_mail_waybill_detail_dt
where dt between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
group by date_time      --日期
       , scan_site_code --扫描站点
       , input_site     --扫描站点
       , agent_code     --代理区code
       , agent_name --代理区名称
 distribute by dt;